﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DataAccess.IService;
using System.Data;
using Utility;
using System.Collections;
using System.Data.SqlClient;
using Entity;
namespace DataAccess.Service
{
    public class AccountService:IAccountService
    {
 
        public void delUserByID(String stuId)
        {
             
            string commandText ="delete from Student where stuId=@stuId";
            SqlParameter[] paras = new SqlParameter[]
            {
                new SqlParameter("stuId",stuId)
            };
            SQLHelper.ExecuteNonQuery(commandText, paras);

        }
        public Student getStudentInform(String stuId)
        {
            Student stuFromDB = new Student();
            string commandText = "select * from Student where stuId=@stuId";
            SqlParameter[] paras = new SqlParameter[]
            {
               new SqlParameter("@stuId", stuId),
              
            };
            SqlDataReader sdReader = SQLHelper.ExecuteReader(commandText, paras);

            if (sdReader.Read())
            {
                stuFromDB.StudentId = Convert.ToString(sdReader["stuId"]);
                stuFromDB.StudentName = Convert.ToString(sdReader["name"]);
                stuFromDB.IDCardNum = Convert.ToString(sdReader["idCard"]);
                stuFromDB.ClassNum = Convert.ToInt32(sdReader["classNum"]);
                stuFromDB.Grade = Convert.ToInt32(sdReader["grade"]);
                stuFromDB.FirstMajor = Convert.ToString(sdReader["firstMajor"]);
                stuFromDB.SecondMajor = Convert.ToString(sdReader["secondMajor"]);
                stuFromDB.Academy= Convert.ToString(sdReader["academy"]);

            }
            return stuFromDB;
        }
        public void update(Student student)
        {

            string commandText = "update Student set stuId=@stuId,studentNaem=@name,idCard=@idCard,classNum=@classNum,passWord=@password where stuId=@stuId";
            SqlParameter[] paras = new SqlParameter[]
            {
               new SqlParameter("stuId", student.StudentId),
               new SqlParameter("name", student.StudentName),
               new SqlParameter("idCard", student.IDCardNum),
               new SqlParameter("classNum", student.ClassNum),
               new SqlParameter("stuentPassword", student.Password)
            };
            SQLHelper.ExecuteNonQuery(commandText, paras);

          
        }

        public void updatePassword(String stuId,String password)
        {
            string commandText = "update Student set pwd=@password where stuId=@stuId";
            SqlParameter[] paras = new SqlParameter[]
            {
                new SqlParameter("stuId", stuId),
                new SqlParameter("password", password)
            };
            SQLHelper.ExecuteNonQuery(commandText, paras);
            
        }



        public Student exist(String stuId, String password)
        {
            Student stuFromDB = new Student();
            string commandText = "select * from Student where pwd=@pwd and stuId=@stuId";
            SqlParameter[] paras = new SqlParameter[]
            {
               new SqlParameter("@stuId", stuId),
               new SqlParameter("@pwd", password)
            };
            SqlDataReader sdReader = SQLHelper.ExecuteReader(commandText, paras);

            if (sdReader.Read())
            {
                stuFromDB.StudentId = Convert.ToString(sdReader["stuId"]);
                stuFromDB.StudentName = Convert.ToString(sdReader["name"]);
                stuFromDB.IDCardNum = Convert.ToString(sdReader["idCard"]);
                stuFromDB.ClassNum = Convert.ToInt32(sdReader["classNum"]);
                stuFromDB.Password = Convert.ToString(sdReader["pwd"]);
                stuFromDB.Grade = Convert.ToInt32(sdReader["grade"]);
                stuFromDB.FirstMajor = Convert.ToString(sdReader["firstMajor"]);
                stuFromDB.SecondMajor = Convert.ToString(sdReader["secondMajor"]);
                stuFromDB.Email = Convert.ToString(sdReader["email"]);

            }
            return stuFromDB;
        }





        //根据身份证查询是否存在
        public Student getStudentByIdCardNum(String idCard)
        {


            Student stuFromDB = new Student();
            string commandText = "select * from Student where idCard=@idCard";
            SqlParameter[] paras = new SqlParameter[]
            {
                 new SqlParameter("idCard", idCard)
            };
            SqlDataReader sdReader = SQLHelper.ExecuteReader(commandText, paras);

            if (sdReader.Read())
            {
                stuFromDB.StudentId = Convert.ToString(sdReader["stuId"]);
                stuFromDB.StudentName = Convert.ToString(sdReader["name"]);
                stuFromDB.IDCardNum = Convert.ToString(sdReader["idCard"]);
                stuFromDB.ClassNum = Convert.ToInt32(sdReader["classNum"]);
                stuFromDB.Password = Convert.ToString(sdReader["pwd"]);
            }
            return stuFromDB;
        }

       
        public string[] getStudentObjectId(String stuId)
        {
            string[] objectId = new string[2];
            string commandText = "select firstMajor,secondMajor from student where stuId=@stuId";
            SqlParameter[] paras = new SqlParameter[]
            {
               new SqlParameter("stuId", stuId)
            };
            SqlDataReader sdReader = SQLHelper.ExecuteReader(commandText, paras);

            if (sdReader.Read())
            {
                objectId[0] = Convert.ToString(sdReader["firstMajor"]);
                objectId[1] = Convert.ToString(sdReader["secondMajor"]);
            }
            return objectId;
        }


    






        public SysAdmin validateAdmin(String loginid, String loginpwd)
        {
            SysAdmin admin = new SysAdmin();
            string commandText = "select * from SysAdmin where LoginId=@LoginId and LoginPwd=@LoginPwd";
            SqlParameter[] paras = new SqlParameter[]
            {
               new SqlParameter("LoginId", loginid),
               new SqlParameter("LoginPwd", loginpwd)
            };
            SqlDataReader sdReader = SQLHelper.ExecuteReader(commandText, paras);

            if (sdReader.Read())
            {
                admin.LoginId = Convert.ToString(sdReader["LoginId"]);
                admin.LoginPwd = Convert.ToString(sdReader["LoginPwd"]);
            }
            return admin;
        }



        public DataTable getStudentTable()
        {
            string CommandText = "select * from Student";
            string[] colNames = new string[]
            {
                "stuId",
                "name",
                "idCard",
                "classNum",
                "grade",
                "pwd",
                "firstMajor",
                "secondMajor",
                "email"
            };
            return SQLHelper.generateDataTable(CommandText, colNames);
        }

        public bool insertStudent(Student student)
        {
            bool result = false;
            string commandText = "Insert into Student(stuId,name,idCard,classNum,grade,pwd,firstMajor,secondMajor,email) values(@stuId,@name,@idCard,@classNum,@grade,@pwd,@firstMajor,@secondMajor,@email)";
            SqlParameter[] paras = new SqlParameter[]
            {
                new SqlParameter("@stuId", student.StudentId),
                new SqlParameter("@name", student.StudentName),
                new SqlParameter("@idCard", student.IDCardNum),
                new SqlParameter("@classNum", student.ClassNum),
                new SqlParameter("@grade", student.Grade),
                new SqlParameter("@pwd", student.Password),
                new SqlParameter("@firstMajor", student.FirstMajor),
                new SqlParameter("@secondMajor", student.SecondMajor),
                new SqlParameter("@email", student.Email)
            };
            if (SQLHelper.ExecuteNonQuery(commandText, paras) > 0)
            {
                result = true;
            }
            return result;
        }
        public bool updateEmail(Student stu, string txtMail)
        {
            bool result = false;
            string commandText = "update Student set email=@email where stuId=@stuId";
            SqlParameter[] paras = new SqlParameter[]
            {
                new SqlParameter("@stuId", stu.StudentId),
                new SqlParameter("@email", txtMail)
            };
            if (SQLHelper.ExecuteNonQuery(commandText, paras) > 0)
            {
                result = true;
            }
            return result;
        }

        /*public bool selectAuthority(int roleId, string authName)
        {
            bool result = false;
            string commandText = "select * from RoleAuthoritySet where roleId=@roleId";
            SqlParameter[] paras = new SqlParameter[]
            {
               new SqlParameter("roleId",roleId),
            };
            SqlDataReader sdReader = SQLHelper.ExecuteReader(commandText, paras);

            if (sdReader.Read())
            {
                result= Convert.ToBoolean(sdReader[authName]);
            }
            return result;
        }

        */











        #region
        public bool selectAuthority(int roleId, string authName)
        {
            bool result = false;
            string commandText = "select * from RoleAuthoritySet where roleId=@roleId";
            SqlParameter[] paras = new SqlParameter[]
            {
               new SqlParameter("roleId",roleId),
            };
            SqlDataReader sdReader = SQLHelper.ExecuteReader(commandText, paras);

            if (sdReader.Read())
            {
                result = Convert.ToBoolean(sdReader[authName]);
            }
            return result;
        }
        public DataSet selectAuthorityDS()
        {
            string commandText = "select * from RoleAuthority";
            return SQLHelper.ExecuteDataSet(commandText);
        }
        public bool deleteRole(int roleId)
        {
            bool result = false;
            string commandText = "delete from RoleAuthority where roleId=@roleId";
            SqlParameter[] paras = new SqlParameter[]
            {
                new SqlParameter("@roleId", roleId),
               
            };
            if (SQLHelper.ExecuteNonQuery(commandText, paras) > 0)
            {
                result = true;
            }
            return result;
        }
        public List<bool> selectRoleInfor(string roleName)
        {
            List<bool> authoritys = new List<bool>();
            string commandText = "select * from RoleAuthority where roleName=@roleName";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("roleName",roleName)
            };
            SqlDataReader sdReader = SQLHelper.ExecuteReader(commandText, paras);
            if (sdReader.Read())
            {
                authoritys.Add(Convert.ToBoolean(sdReader["systemSet"]));
                authoritys.Add(Convert.ToBoolean(sdReader["announceManage"]));
                authoritys.Add(Convert.ToBoolean(sdReader["replayPro"]));
                authoritys.Add(Convert.ToBoolean(sdReader["proManage"]));
                authoritys.Add(Convert.ToBoolean(sdReader["proPost"]));
                authoritys.Add(Convert.ToBoolean(sdReader["utilityManage"]));
                authoritys.Add(Convert.ToBoolean(sdReader["utilityPost"]));
                authoritys.Add(Convert.ToBoolean(sdReader["networkManage"]));
                authoritys.Add(Convert.ToBoolean(sdReader["stuCardManage"]));
            }
            return authoritys;
        }
        public bool updateRoleAuthority(List<bool> authoritys, String roleName)
        {
            bool result = false;
            string commandText = "update RoleAuthority set systemSet=@systemSet,announceManage=@announceManage,replayPro=@replayPro,proManage=@proManage,proPost=@proPost,utilityManage=@utilityManage,utilityPost=@utilityPost,networkManage=@networkManage,stuCardManage=@stuCardManage where roleName=@roleName";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("roleName",roleName),
                new SqlParameter("systemSet",Convert.ToByte(authoritys[0])),
                new SqlParameter("announceManage",Convert.ToByte(authoritys[1])),
                new SqlParameter("replayPro",Convert.ToByte(authoritys[2])),
                new SqlParameter("proManage",Convert.ToByte(authoritys[3])),
                new SqlParameter("proPost",Convert.ToByte(authoritys[4])),
                new SqlParameter("utilityManage",Convert.ToByte(authoritys[5])),
                new SqlParameter("utilityPost",Convert.ToByte(authoritys[6])),
                new SqlParameter("networkManage",Convert.ToByte(authoritys[7])),
                new SqlParameter("stuCardManage",Convert.ToByte(authoritys[8]))
            };
            if (SQLHelper.ExecuteNonQuery(commandText, paras) > 0)
            {
                result = true;
            }
            return result;
        }
        public bool insertRole(List<bool> authoritys, String roleName)
        {
            bool result = false;
            string commandText = "insert into RoleAuthority(roleName,systemSet,announceManage,replayPro,proManage,proPost,utilityManage,utilityPost,networkManage,stuCardManage) values(@roleName,@systemSet,@announceManage,@replayPro,@proManage,@proPost,@utilityManage,@utilityPost,@networkManage,@stuCardManage)";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("roleName",roleName),
                new SqlParameter("systemSet",Convert.ToByte(authoritys[0])),
                new SqlParameter("announceManage",Convert.ToByte(authoritys[1])),
                new SqlParameter("replayPro",Convert.ToByte(authoritys[2])),
                new SqlParameter("proManage",Convert.ToByte(authoritys[3])),
                new SqlParameter("proPost",Convert.ToByte(authoritys[4])),
                new SqlParameter("utilityManage",Convert.ToByte(authoritys[5])),
                new SqlParameter("utilityPost",Convert.ToByte(authoritys[6])),
                new SqlParameter("networkManage",Convert.ToByte(authoritys[7])),
                new SqlParameter("stuCardManage",Convert.ToByte(authoritys[8]))
            };
            if (SQLHelper.ExecuteNonQuery(commandText, paras) > 0)
            {
                result = true;
            }
            return result;
        }
        #endregion
        #region
        public bool addOperator(Operator oper)
        {
            bool result = false;
            string commandText = "Insert into Operator(operatorName,deptId,loginId,loginPwd,roleId) values(@operatorName,@deptId,@loginId,@loginPwd,@roleId)";
            SqlParameter[] paras = new SqlParameter[]
            {
                new SqlParameter("@operatorName", oper.OperatorName),
                new SqlParameter("@deptId", oper.DeptId),
                new SqlParameter("@loginId", oper.LoginId),
                new SqlParameter("@loginPwd", oper.LoginPwd),
                new SqlParameter("@roleId", oper.RoleId),
            };
            if (SQLHelper.ExecuteNonQuery(commandText, paras) > 0)
            {
                result = true;
            }
            return result;
        }
        public DataSet selectOperatorDataSet()
        {
            String commandText = "select * from Operator,Department,RoleAuthority where Operator.deptId=Department.deptId and Operator.roleId=RoleAuthority.roleId";
            return SQLHelper.ExecuteDataSet(commandText);
        }
        public bool deleteOperator(int id)
        {
            bool result = false;
            string commandText = "delete from Operator where operatorId=@id";
            SqlParameter[] paras = new SqlParameter[]
            {
                new SqlParameter("@id", id),
               
            };
            if (SQLHelper.ExecuteNonQuery(commandText, paras) > 0)
            {
                result = true;
            }
            return result;
        }
        public Operator selectOperator(int operatorId)
        {
            Operator oper = new Operator();
            string commandText = "select * from Operator where operatorId=@operatorId";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("operatorId",operatorId)
            };
            SqlDataReader sdReader = SQLHelper.ExecuteReader(commandText, paras);
            if (sdReader.Read())
            {
                oper.OperatorName = Convert.ToString(sdReader["operatorName"]);
                oper.DeptId = Convert.ToInt32(sdReader["deptId"]);
                oper.RoleId = Convert.ToInt32(sdReader["roleId"]);

            }
            return oper;
        }
        public bool updateOperator(int operatorId, Operator oper)
        {
            bool result = false;
            string commandText = "update Operator set operatorName=@operatorName,deptId=@deptId,roleId=@roleId where operatorId=@operatorId";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("operatorName",oper.OperatorName),
                new SqlParameter("deptId",oper.DeptId),
                new SqlParameter("roleId",oper.RoleId),
                new SqlParameter("operatorId",operatorId)
            };
            if (SQLHelper.ExecuteNonQuery(commandText, paras) > 0)
            {
                result = true;
            }
            return result;
        }
        #endregion
        
    }
}
